package com.eascs.admin.o2o.badloan.dao;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.List;

import org.springframework.stereotype.Repository;

import com.eascs.admin.common.dao.BaseO2oDao;
import com.eascs.admin.o2o.badloan.entity.O2oBadLoanPageVo;
import com.eascs.admin.o2o.badloan.entity.O2oBadRecordEntity;
import com.eascs.admin.o2o.loan.entity.O2oEaLoanDataEnity;
import com.eascs.admin.o2o.loan.entity.O2oEaLoanDataTdEnity;
import com.eascs.app.dao.PageResult;
import com.eascs.app.dao.SqlPageQuery;
import com.eascs.common.util.DateUtils;
import com.eascs.common.util.StringUtil;
import com.eascs.loan.form.O2oPaymentsForm;


/**
 * 
 * @author CXW
 *
 */

@Repository
public class O2oBadLoanDao extends BaseO2oDao{

	public PageResult<Object[]> getByPage(O2oBadLoanPageVo vo){
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, vo.getPageSize(), vo.getPageIndex());
		StringBuffer querySql = new StringBuffer();
		querySql.append(" select oeld.id,oll.loanapply_no,oeld.bill_code_ea,oeld.bill_code,opr.province_name, ");
		querySql.append(" oeld.loaner_name,CONCAT(oll.ltd_code,'-',oll.ltd_name)  as ltd_name,oeld.pay_type,oeld.loan_type, ");
		querySql.append(" DATE_FORMAT(oeld.pay_date,'%Y-%m-%d'),oeld.pay_amount,oeld.payment_amount,DATE_FORMAT(");
		querySql.append(" (SELECT MAX(bbtr.this_payment_date) FROM o2o_badloan_record bbtr WHERE ");
		querySql.append(" bbtr.flow_status='CLO' and bbtr.bill_code= oeld.bill_code_ea and bbtr.loan_data_id=oeld.id),'%Y-%m-%d') as end_date,  ");
		querySql.append(" case oels.flow_status when 'CLO' then  '已结清' else '未结清' end as flow_status ");
		querySql.append(" from o2o_ea_loan_data oeld LEFT JOIN o2o_loanapply oll on ");
		querySql.append(" oll.bill_code=oeld.bill_code_ea LEFT JOIN o2o_platform_regional opr on oll.ltd = opr.platform_id ");
		querySql.append(" LEFT JOIN o2o_ea_loan_settle oels on oeld.id=oels.loan_data_id ");
		querySql.append(" where /**DATA_PERMISSION(oeld.ltd)**/ 1=1 and oeld.pay_type = 'DF' ");
		if(!StringUtil.isEmpty(vo.getBillCode())){
			querySql.append(" and oeld.bill_code like ?");
			sqlPageQuery.addLikeParam(vo.getBillCode());
		}
		if(!StringUtil.isEmpty(vo.getBillcodeea())){
			querySql.append(" and oeld.bill_code_ea like ?");
			sqlPageQuery.addLikeParam(vo.getBillcodeea());
		}
		if(!StringUtil.isEmpty(vo.getLoanType())){
			//querySql.append(" and oeld.loan_type = ? ");
			querySql.append(" and oeld.loan_type in (");
			String loanTypeA [] = {};  
			String loanType = "";
			StringBuffer loanTypes = new StringBuffer();
			if(vo.getLoanType().length()>4){
				loanTypeA = vo.getLoanType().split(",");
				for (int i = 0; i < loanTypeA.length; i++) {
					loanTypes.append("'" + loanTypeA[i] + "',");
				}
			}else{
				loanTypes.append("'"+vo.getLoanType()+"',");
			}
			loanType = loanTypes.toString().substring(0,loanTypes.toString().length()-1);
			querySql.append(loanType);
			querySql.append(")");
		}
		if(!StringUtil.isEmpty(vo.getLoanerName())){
			querySql.append(" and oeld.loaner_name like ?");
			sqlPageQuery.addLikeParam(vo.getLoanerName());
		}
		/*if(!StringUtil.isEmpty(vo.getProvince())){
			querySql.append(" and oeld.province = ? ");
			sqlPageQuery.addParam(vo.getProvince());
		}*/
		if(!StringUtil.isEmpty(vo.getHostProvince())){
			querySql.append(" and opr.province_code = ? ");
			sqlPageQuery.addParam(vo.getHostProvince());
		}
		
		if (!StringUtil.isEmpty(vo.getPayDateStart())) {
			querySql.append(" AND oeld.pay_date >= ?");
			sqlPageQuery.addParam(vo.getPayDateStart());
		}
		if (!StringUtil.isEmpty(vo.getPayDateEnd())) {
			querySql.append(" AND oeld.pay_date < ?");
			sqlPageQuery.addParam(DateUtils.formateStandDate(DateUtils.addDate(DateUtils.formateStandDate(vo.getPayDateEnd()), 1)));
		}
		
		if(!StringUtil.isEmpty(vo.getBankType())){
			querySql.append(" and oll.bank_type = ? ");
			sqlPageQuery.addParam(vo.getBankType());
		}
		
		if(!StringUtil.isEmpty(vo.getFlowStatus())){
			if (vo.getFlowStatus().equals("CLO")) {
				querySql.append(" and oels.flow_status = 'CLO'");
			} else {
				querySql.append(" and (oels.flow_status != 'CLO' or oels.flow_status is null)");
			}
//			sqlPageQuery.addParam(vo.getFlowStatus());
		}
		
		querySql.append(" order by oeld.oprate_time desc ");
		sqlPageQuery.setQuerySql(querySql.toString());
		return this.findDataWithPage(sqlPageQuery);
	}
	
	public PageResult<Object[]> getByPageExport(O2oBadLoanPageVo vo){
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, vo.getPageSize(), vo.getPageIndex());
		StringBuffer querySql = new StringBuffer();
		querySql.append(" select oeld.bill_code_ea,oeld.bill_code,opr.province_name, ");
		querySql.append(" oeld.loaner_name,CONCAT(oll.ltd_code,'-',oll.ltd_name)  as ltd_name, ");
		querySql.append(" (SELECT pdo.item_name FROM pub_dditem AS pdo WHERE pdo.item_code = oeld.pay_type AND pdo.dd_no = '11340') as pay_type, ");
		querySql.append(" (SELECT pdo.item_name FROM pub_dditem_o2o AS pdo WHERE pdo.item_code = oeld.loan_type AND pdo.dd_no = 'lhdch77b1ac1d') as loan_type, ");
		querySql.append(" DATE_FORMAT(oeld.pay_date,'%Y-%m-%d'),oeld.pay_amount,oeld.payment_amount,DATE_FORMAT(");
		querySql.append(" (SELECT MAX(bbtr.this_payment_date) FROM o2o_badloan_record bbtr WHERE ");
		querySql.append(" bbtr.flow_status='CLO' and bbtr.bill_code= oeld.bill_code_ea),'%Y-%m-%d') as end_date  ");
		querySql.append(" from o2o_ea_loan_data oeld LEFT JOIN o2o_loanapply oll on oll.bill_code=oeld.bill_code_ea LEFT JOIN o2o_platform_regional opr on oeld.ltd = opr.platform_id ");
		querySql.append(" where /**DATA_PERMISSION(oeld.ltd)**/ 1=1 and oeld.pay_type = 'DF'");
		if(!StringUtil.isEmpty(vo.getBillCode())){
			querySql.append(" and oeld.bill_code like ?");
			sqlPageQuery.addLikeParam(vo.getBillCode());
		}
		if(!StringUtil.isEmpty(vo.getBillcodeea())){
			querySql.append(" and oeld.bill_code_ea like ?");
			sqlPageQuery.addLikeParam(vo.getBillcodeea());
		}
		if(!StringUtil.isEmpty(vo.getLoanType())){
			//querySql.append(" and oeld.loan_type = ? ");
			querySql.append(" and oeld.loan_type in (");
			String loanTypeA [] = {};  
			String loanType = "";
			StringBuffer loanTypes = new StringBuffer();
			if(vo.getLoanType().length()>4){
				loanTypeA = vo.getLoanType().split(",");
				for (int i = 0; i < loanTypeA.length; i++) {
					loanTypes.append("'" + loanTypeA[i] + "',");
				}
			}else{
				loanTypes.append("'"+vo.getLoanType()+"',");
			}
			loanType = loanTypes.toString().substring(0,loanTypes.toString().length()-1);
			querySql.append(loanType);
			querySql.append(")");
		}
		if(!StringUtil.isEmpty(vo.getLoanerName())){
			querySql.append(" and oeld.loaner_name like ?");
			sqlPageQuery.addLikeParam(vo.getLoanerName());
		}
		/*if(!StringUtil.isEmpty(vo.getProvince())){
			querySql.append(" and oeld.province = ? ");
			sqlPageQuery.addParam(vo.getProvince());
		}*/
		if(!StringUtil.isEmpty(vo.getHostProvince())){
			querySql.append(" and opr.province_code = ? ");
			sqlPageQuery.addParam(vo.getHostProvince());
		}
		if (!StringUtil.isEmpty(vo.getPayDateStart())) {
			querySql.append(" AND oeld.pay_date >= ?");
			sqlPageQuery.addParam(vo.getPayDateStart());
		}
		if (!StringUtil.isEmpty(vo.getPayDateEnd())) {
			querySql.append(" AND oeld.pay_date < ?");
			sqlPageQuery.addParam(DateUtils.formateStandDate(DateUtils.addDate(DateUtils.formateStandDate(vo.getPayDateEnd()), 1)));
		}
		if(!StringUtil.isEmpty(vo.getBankType())){
			querySql.append(" and oll.bank_type = ? ");
			sqlPageQuery.addParam(vo.getBankType());
		}
		querySql.append(" order by oeld.oprate_time desc ");
		sqlPageQuery.setQuerySql(querySql.toString());
		return this.findDataWithPage(sqlPageQuery);
	}
	
	
	
	public PageResult<Object[]> getByFqJl(O2oBadLoanPageVo vo){
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, vo.getPageSize(), vo.getPageIndex());
		StringBuffer querySql = new StringBuffer();
		querySql.append(" select oeld.id,oll.loanapply_no,oeld.bill_code_ea,oeld.bill_code,opr.province_name, ");
		querySql.append(" oeld.loaner_name,CONCAT(oll.ltd_code,'-',oll.ltd_name)  as ltd_name,oeld.pay_type,oeld.loan_type, ");
		querySql.append(" DATE_FORMAT(oeld.pay_date,'%Y-%m-%d'),oeld.pay_amount,oeld.payment_amount,DATE_FORMAT(");
		querySql.append(" (SELECT MAX(bbtr.this_payment_date) FROM o2o_badloan_record bbtr WHERE ");
		querySql.append(" bbtr.flow_status='CLO' and bbtr.bill_code= oeld.bill_code_ea and bbtr.loan_data_id=oeld.id),'%Y-%m-%d') as end_date,  ");
		querySql.append(" case oels.flow_status when 'CLO' then  '已结清' else '未结清' end as flow_status ");
		querySql.append(" from o2o_ea_loan_data oeld LEFT JOIN o2o_loanapply oll on ");
		querySql.append(" oll.bill_code=oeld.bill_code_ea LEFT JOIN o2o_platform_regional opr on oll.ltd = opr.platform_id ");
		querySql.append(" LEFT JOIN o2o_ea_loan_settle oels on oeld.id=oels.loan_data_id ");
		querySql.append(" where /**DATA_PERMISSION(oeld.ltd)**/ 1=1 and oeld.pay_type = 'ZD' ");
		if(!StringUtil.isEmpty(vo.getBillCode())){
			querySql.append(" and oeld.bill_code like ?");
			sqlPageQuery.addLikeParam(vo.getBillCode());
		}
		if(!StringUtil.isEmpty(vo.getBillcodeea())){
			querySql.append(" and oeld.bill_code_ea like ?");
			sqlPageQuery.addLikeParam(vo.getBillcodeea());
		}
		if(!StringUtil.isEmpty(vo.getLoanType())){
			//querySql.append(" and oeld.loan_type = ? ");
			querySql.append(" and oeld.loan_type in (");
			String loanTypeA [] = {};  
			String loanType = "";
			StringBuffer loanTypes = new StringBuffer();
			if(vo.getLoanType().length()>4){
				loanTypeA = vo.getLoanType().split(",");
				for (int i = 0; i < loanTypeA.length; i++) {
					loanTypes.append("'" + loanTypeA[i] + "',");
				}
			}else{
				loanTypes.append("'"+vo.getLoanType()+"',");
			}
			loanType = loanTypes.toString().substring(0,loanTypes.toString().length()-1);
			querySql.append(loanType);
			querySql.append(")");
		}
		if(!StringUtil.isEmpty(vo.getLoanerName())){
			querySql.append(" and oeld.loaner_name like ?");
			sqlPageQuery.addLikeParam(vo.getLoanerName());
		}
		/*if(!StringUtil.isEmpty(vo.getProvince())){
			querySql.append(" and oeld.province = ? ");
			sqlPageQuery.addParam(vo.getProvince());
		}*/
		if(!StringUtil.isEmpty(vo.getHostProvince())){
			querySql.append(" and opr.province_code = ? ");
			sqlPageQuery.addParam(vo.getHostProvince());
		}
		
		if (!StringUtil.isEmpty(vo.getPayDateStart())) {
			querySql.append(" AND oeld.pay_date >= ?");
			sqlPageQuery.addParam(vo.getPayDateStart());
		}
		if (!StringUtil.isEmpty(vo.getPayDateEnd())) {
			querySql.append(" AND oeld.pay_date < ?");
			sqlPageQuery.addParam(DateUtils.formateStandDate(DateUtils.addDate(DateUtils.formateStandDate(vo.getPayDateEnd()), 1)));
		}
		
		if(!StringUtil.isEmpty(vo.getBankType())){
			querySql.append(" and oll.bank_type = ? ");
			sqlPageQuery.addParam(vo.getBankType());
		}
		
		querySql.append(" order by oeld.oprate_time desc ");
		sqlPageQuery.setQuerySql(querySql.toString());
		return this.findDataWithPage(sqlPageQuery);
	}
	
	/**
	 * @Title: getByFqJlPageExport
	 * @Description: 分期接力数据
	 * @author 张庆奇
	 * @param vo
	 * @return
	 * @return PageResult<Object[]>
	 * @throws
	 * @date 2017年5月23日 下午4:46:36
	 */
	public PageResult<Object[]> getByFqJlPageExport(O2oBadLoanPageVo vo){
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, vo.getPageSize(), vo.getPageIndex());
		StringBuffer querySql = new StringBuffer();
		querySql.append(" select oeld.bill_code_ea,oeld.bill_code,opr.province_name, ");
		querySql.append(" oeld.loaner_name,CONCAT(oll.ltd_code,'-',oll.ltd_name)  as ltd_name, ");
		querySql.append(" (SELECT pdo.item_name FROM pub_dditem AS pdo WHERE pdo.item_code = oeld.pay_type AND pdo.dd_no = '11340') as pay_type, ");
		querySql.append(" (SELECT pdo.item_name FROM pub_dditem_o2o AS pdo WHERE pdo.item_code = oeld.loan_type AND pdo.dd_no = 'lhdch77b1ac1d') as loan_type, ");
		querySql.append(" DATE_FORMAT(oeld.pay_date,'%Y-%m-%d'),oeld.pay_amount,oeld.payment_amount,DATE_FORMAT(");
		querySql.append(" (SELECT MAX(bbtr.this_payment_date) FROM o2o_badloan_record bbtr WHERE ");
		querySql.append(" bbtr.flow_status='CLO' and bbtr.bill_code= oeld.bill_code_ea),'%Y-%m-%d') as end_date  ");
		querySql.append(" from o2o_ea_loan_data oeld LEFT JOIN o2o_loanapply oll on oll.bill_code=oeld.bill_code_ea LEFT JOIN o2o_platform_regional opr on oeld.ltd = opr.platform_id ");
		querySql.append(" where /**DATA_PERMISSION(oeld.ltd)**/ 1=1 and oeld.pay_type = 'ZD'");
		if(!StringUtil.isEmpty(vo.getBillCode())){
			querySql.append(" and oeld.bill_code like ?");
			sqlPageQuery.addLikeParam(vo.getBillCode());
		}
		if(!StringUtil.isEmpty(vo.getBillcodeea())){
			querySql.append(" and oeld.bill_code_ea like ?");
			sqlPageQuery.addLikeParam(vo.getBillcodeea());
		}
		if(!StringUtil.isEmpty(vo.getLoanType())){
			//querySql.append(" and oeld.loan_type = ? ");
			querySql.append(" and oeld.loan_type in (");
			String loanTypeA [] = {};  
			String loanType = "";
			StringBuffer loanTypes = new StringBuffer();
			if(vo.getLoanType().length()>4){
				loanTypeA = vo.getLoanType().split(",");
				for (int i = 0; i < loanTypeA.length; i++) {
					loanTypes.append("'" + loanTypeA[i] + "',");
				}
			}else{
				loanTypes.append("'"+vo.getLoanType()+"',");
			}
			loanType = loanTypes.toString().substring(0,loanTypes.toString().length()-1);
			querySql.append(loanType);
			querySql.append(")");
		}
		if(!StringUtil.isEmpty(vo.getLoanerName())){
			querySql.append(" and oeld.loaner_name like ?");
			sqlPageQuery.addLikeParam(vo.getLoanerName());
		}
		/*if(!StringUtil.isEmpty(vo.getProvince())){
			querySql.append(" and oeld.province = ? ");
			sqlPageQuery.addParam(vo.getProvince());
		}*/
		if(!StringUtil.isEmpty(vo.getHostProvince())){
			querySql.append(" and opr.province_code = ? ");
			sqlPageQuery.addParam(vo.getHostProvince());
		}
		if (!StringUtil.isEmpty(vo.getPayDateStart())) {
			querySql.append(" AND oeld.pay_date >= ?");
			sqlPageQuery.addParam(vo.getPayDateStart());
		}
		if (!StringUtil.isEmpty(vo.getPayDateEnd())) {
			querySql.append(" AND oeld.pay_date < ?");
			sqlPageQuery.addParam(DateUtils.formateStandDate(DateUtils.addDate(DateUtils.formateStandDate(vo.getPayDateEnd()), 1)));
		}
		if(!StringUtil.isEmpty(vo.getBankType())){
			querySql.append(" and oll.bank_type = ? ");
			sqlPageQuery.addParam(vo.getBankType());
		}
		querySql.append(" order by oeld.oprate_time desc ");
		sqlPageQuery.setQuerySql(querySql.toString());
		return this.findDataWithPage(sqlPageQuery);
	}
	
	public PageResult<Object[]> paymentsList(O2oPaymentsForm vo){
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, vo.getPageSize(), vo.getShowPage());
		StringBuilder sql = new StringBuilder();
		SimpleDateFormat s = new SimpleDateFormat("yyyy-MM-dd");
		sql.append(" SELECT oeldt.id,oeld.bill_code_ea,oeld.loaner_name,oeld.loaner_card, ");
		sql.append(" oeldt.periods,oeldt.payments_date,oeldt.residual_principal, ");
		sql.append(" oeldt.pay_principal,oeldt.pay_interest,oeldt.repayment_status FROM");
		sql.append(" o2o_ea_loan_data_td oeldt JOIN o2o_ea_loan_data oeld ON oeldt.main_id = oeld.id");
		sql.append(" WHERE oeldt.IS_DEL = 'N' ");

		// 单据编号
		if (!StringUtil.isEmpty(vo.getBillCode())) {
			sql.append(" AND oeld.BILL_CODE_EA = ? ");
			sqlPageQuery.addParam(vo.getBillCode());
		}
		// 主键
		if (!StringUtil.isEmpty(vo.getPayments_id())) {
			sql.append(" AND oeldt.ID = ?");
			sqlPageQuery.addParam(vo.getPayments_id());
		}
		// 计划还款日期 begin
		if (!StringUtil.isEmpty(vo.getBeginPaymentsDate())) {
			sql.append(" AND oeldt.PAYMENTS_DATE>= ?");
			try {
				sqlPageQuery.addParam(s.parse(vo.getBeginPaymentsDate()));
			} catch (ParseException e) {
				e.printStackTrace();
			}
		}
		// 计划还款日期 end
		if (!StringUtil.isEmpty(vo.getEndPaymentsDate())) {
			sql.append(" AND oeldt.PAYMENTS_DATE<= ?");
			try {
				sqlPageQuery.addParam(s.parse(vo.getEndPaymentsDate()));
			} catch (ParseException e) {
				e.printStackTrace();
			}
		}
		sql.append(" ORDER BY oeldt.PERIODS ASC");
		sqlPageQuery.setQuerySql(sql.toString());
		PageResult<Object[]> loanBasepage = this.findDataWithPage(sqlPageQuery);
		return loanBasepage;
	}
	
	public PageResult<Object[]> backPlan(O2oPaymentsForm vo){
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, vo.getPageSize(), vo.getShowPage());
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT oeldt.id,oeldt.periods,oeldt.payments_date,oeldt.residual_principal, ");
		sql.append(" oeldt.pay_principal,oeldt.pay_interest,oeldt.repayment_status FROM");
		sql.append(" o2o_ea_loan_data_td oeldt WHERE oeldt.IS_DEL = 'N' ");
		if (!StringUtil.isEmpty(vo.getBillCode())) {
			sql.append(" AND  oeldt.main_id = ? ");
			sqlPageQuery.addParam(vo.getBillCode());
		}
		sql.append(" ORDER BY oeldt.PERIODS ASC");
		sqlPageQuery.setQuerySql(sql.toString());
		PageResult<Object[]> loanBasepage = this.findDataWithPage(sqlPageQuery);
		return loanBasepage;
	}
	
	public O2oEaLoanDataEnity findByBillCode(String loanDataId){
		StringBuffer sql = new StringBuffer();
		sql.append(" select oeld.* from o2o_ea_loan_data AS oeld where oeld.id= ? ");
		return this.findEntity(sql.toString(), O2oEaLoanDataEnity.class, loanDataId);
	}
	
	public O2oEaLoanDataEnity findDataByBillCode(String billCode){
		StringBuffer sql = new StringBuffer();
		sql.append("select oeld.* from o2o_ea_loan_data AS oeld where oeld.bill_code_ea=? ");
		return this.findEntity(sql.toString(), O2oEaLoanDataEnity.class, billCode);
	}
	
	public O2oEaLoanDataTdEnity findDataTdByBillCode(String billCode){
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT oeldt.id,oeldt.main_id,oeldt.periods,max(oeldt.payments_date) as payments_date ");
		sql.append(" ,oeldt.residual_principal,oeldt.pay_principal,oeldt.pay_interest,oeldt.pay_total,oeldt.is_del, ");
		sql.append(" oeldt.data_type,oeldt.oprate_time,oeldt.oprate_psn,oeldt.repayment_status,oeldt.pay_type ");
		sql.append(" FROM o2o_ea_loan_data_td AS oeldt INNER JOIN o2o_ea_loan_data AS oeld ON oeldt.main_id = oeld.id ");
		sql.append(" WHERE oeldt.is_del = 'N' AND oeld.bill_code_ea = ? GROUP BY oeld.bill_code ");
		return this.findEntity(sql.toString(), O2oEaLoanDataTdEnity.class, billCode);
	}
	
	public List<O2oBadRecordEntity> findRecordByBillCode(String billCde, String loanDataId){
		StringBuffer sql = new StringBuffer();
		sql.append("select * from o2o_badloan_record obr where obr.loanapply_billcode=?  AND OBR.LOAN_DATA_ID = ? ");
		return this.findEntitys(sql.toString(), O2oBadRecordEntity.class, billCde, loanDataId);
	}
	
	public O2oBadRecordEntity findRecordTotal(String billCde, String loanDataId){
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT SUM(THIS_PAYMENT_CAPITAL) AS THIS_PAYMENT_CAPITAL,");
		sql.append(" SUM(THIS_PAYMENT_INTEREST) AS THIS_PAYMENT_INTEREST,");
		sql.append(" NOW_ARREARS");
		sql.append(" FROM O2O_BADLOAN_RECORD OBR WHERE OBR.BILL_CODE=? AND OBR.LOAN_DATA_ID = ? ");
		return this.findEntity(sql.toString(), O2oBadRecordEntity.class, billCde, loanDataId);
	}
	
	public O2oBadRecordEntity findRecordByFlowStatus(O2oBadRecordEntity entity){
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT * FROM O2O_BADLOAN_RECORD OBR WHERE OBR.BILL_CODE=? AND OBR.FLOW_STATUS=? AND OBR.LOAN_DATA_ID=? ");
		return this.findEntity(sql.toString(), O2oBadRecordEntity.class, entity.getBillCode(), entity.getFlowStatus(), entity.getLoanDataId());
	}
	
	public String getNowAreas(String billCode, String loanDataId){
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT (oeld.pay_amount-(SELECT sum(obr.pay_amount) from o2o_badloan_record obr ");
		sql.append(" where obr.loan_data_id = oeld.id)) as new_arear FROM ");
		sql.append(" o2o_ea_loan_data oeld WHERE oeld.bill_code_ea = ? and oeld.id = ? ");
		return this.findString(sql.toString(), billCode, loanDataId);
	}
	
	public int updatePayAreas(String loanDataId){
		StringBuffer sql = new StringBuffer();
		sql.append(" update o2o_ea_loan_data oeldr join ( ");
		sql.append(" SELECT obr.now_arrears,obr.pay_time,obr.loan_data_id ");
		sql.append(" from o2o_badloan_record obr join (SELECT MAX(obrr.pay_time) ");
		sql.append(" as pay_time,obrr.loan_data_id from o2o_badloan_record obrr ");
		sql.append(" where obrr.flow_status='CLO' and obrr.is_delete='N' AND obrr.loan_data_id=? GROUP BY  obrr.loan_data_id ");
		sql.append(" ) as tt on tt.loan_data_id=obr.loan_data_id and  ");
		sql.append(" tt.pay_time=obr.pay_time GROUP BY obr.loan_data_id) ");
		sql.append(" as ole on ole.loan_data_id=oeldr.id set oeldr.pay_areas = ole.now_arrears ");
		return this.execute(sql.toString(), loanDataId);
	}
	
	public PageResult<Object[]> getBadLoanPage(O2oBadLoanPageVo vo){
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, vo.getPageSize(), 0);
		StringBuffer querySql = new StringBuffer();
		querySql.append(" select oeld.id,oll.loanapply_no,oeld.bill_code_ea,oeld.bill_code,");
		querySql.append(" oeld.loaner_name,oeld.pay_type,oeld.loan_type, ");
		querySql.append(" DATE_FORMAT(oeld.pay_date,'%Y-%m-%d'),oeld.pay_amount,oeld.payment_amount,oeld.pay_amount-oeld.payment_amount,DATE_FORMAT(");
		querySql.append(" (SELECT MAX(bbtr.this_payment_date) FROM o2o_badloan_record bbtr WHERE ");
		querySql.append(" bbtr.flow_status='CLO' and bbtr.bill_code= oeld.bill_code_ea and bbtr.loan_data_id=oeld.id),'%Y-%m-%d') as end_date,  ");
		querySql.append(" oeld.pay_areas from o2o_ea_loan_data oeld LEFT JOIN o2o_loanapply oll on ");
		querySql.append(" oll.bill_code=oeld.bill_code_ea LEFT JOIN o2o_platform_regional opr on oll.ltd = opr.platform_id where 1=1 and oeld.loaner_card in(?, ?) ");
//		querySql.append(" GROUP BY oeld.bill_code_ea ");
		querySql.append(" order by oeld.oprate_time desc ");
		sqlPageQuery.addParam(vo.getIdCard());
		sqlPageQuery.addParam(vo.getSpouseIdCard());
		sqlPageQuery.setQuerySql(querySql.toString());
		return this.findDataWithPage(sqlPageQuery);
	}
}
